package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

public class DbInit { // 数据库初始化工具类 完成人：姜凯严 7/1
    public static void main(String[] args) {
        String url = "jdbc:sqlite:redwine.db"; // 数据库文件名，可根据需要调整路径
        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt = conn.createStatement()) {

            String userTable = "CREATE TABLE IF NOT EXISTS user (" +
                    "user_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "username VARCHAR(50) NOT NULL UNIQUE," + // 用户名限制50字符，添加唯一约束
                    "password VARCHAR(100) NOT NULL," + // 密码限制100字符（考虑加密后长度）
                    "address VARCHAR(200)," + // 地址限制200字符
                    "phone VARCHAR(20)," + // 电话限制20字符
                    "balance REAL DEFAULT 0.0 CHECK (balance >= 0)," + // 余额不能为负
                    "permission_level VARCHAR(10) NOT NULL DEFAULT 'normal' CHECK (permission_level IN ('normal', 'admin'))" + // 权限枚举限制
                    ");";

            String redwineTable = "CREATE TABLE IF NOT EXISTS redwine (" +
                    "redwine_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "name VARCHAR(100) NOT NULL," + // 红酒名称限制100字符
                    "stock INTEGER DEFAULT 0 CHECK (stock >= 0)," + // 库存不能为负
                    "price REAL CHECK (price >= 0)," + // 价格不能为负
                    "country VARCHAR(50)," + // 国家限制50字符
                    "city VARCHAR(50)," + // 城市限制50字符
                    "county VARCHAR(50)" + // 县区限制50字符
                    ");";

            String orderTable = "CREATE TABLE IF NOT EXISTS 'order' (" +
                    "order_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "user_id INTEGER NOT NULL," + // 添加非空约束
                    "redwine_id INTEGER NOT NULL," + // 添加非空约束
                    "quantity INTEGER NOT NULL CHECK (quantity > 0)," + // 数量必须大于0
                    "status VARCHAR(20) NOT NULL DEFAULT '未付款' CHECK (status IN ('未付款', '已付款', '待收货', '已完成'))," + // 状态枚举限制
                    "online INTEGER NOT NULL DEFAULT 0 CHECK (online IN (0, 1))," + // 布尔值限制
                    "code INTEGER," + // 订单码保持整型
                    "FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE," + // 级联删除
                    "FOREIGN KEY (redwine_id) REFERENCES redwine(redwine_id) ON DELETE CASCADE" + // 级联删除
                    ");";

            stmt.execute(userTable);
            stmt.execute(redwineTable);
            stmt.execute(orderTable);

            // 插入管理员用户（如果不存在）- 使用 PreparedStatement
            String adminInsert = "INSERT OR IGNORE INTO user (user_id, username, password, address, phone, balance, permission_level) VALUES (?, ?, ?, ?, ?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(adminInsert)) {
                pstmt.setInt(1, 0);
                pstmt.setString(2, "admin");
                pstmt.setString(3, "admin");
                pstmt.setString(4, "管理员地址");
                pstmt.setString(5, "00000000000");
                pstmt.setDouble(6, 0.0);
                pstmt.setString(7, "admin");
                pstmt.executeUpdate();
            }

            System.out.println("数据库初始化完成！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
